const db = require('../db/index')

// 存入工位
exports.setData = (req, res) => {
  console.log("数据");
  console.log(req.body.data);
  try {
      // 假设 req.body 是一个包含多个对象的数组
      req.body.data.map( (item) => {
          if (item.id !== null) {
              // 如果有id，执行更新操作
              const sqlUpdate = `UPDATE workstations
                  SET left_position = ?, top_position = ?, width = ?, height = ?, angle = ?, workStationName = ?, user_id = ?, customType = ?, floorNumber = ?
                  WHERE id = ?;`;
               db.query(sqlUpdate, [
                  item.left,
                  item.top,
                  item.width,
                  item.height,
                  item.angle,
                  item.workStationName,
                  item.name,
                  item.customType,
                  item.floorNumber,
                  item.id
              ], (err, result) => {
                console.log('修改', result);
              });
          } else {
              // 如果没有id，执行添加操作
              const sqlInsert = `INSERT INTO workstations
                  (left_position, top_position, width, height, angle, workStationName, user_id, customType, floorNumber)
                  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
               db.query(sqlInsert, [
                item.left,
                item.top,
                item.width,
                item.height,
                item.angle,
                item.workStationName,
                item.name,
                item.customType,
                item.floorNumber
              ], (err, result) => {
                if (err) {
                  console.log('错误', err);
                }
                console.log('添加', result);
              });
          }
      });

      res.status(200).send('添加成功');
  } catch (error) {
      // 处理任何数据库操作中的错误
      res.status(500).send(`Database error: ${error.message}`);
  }
};

// 返回工位
exports.getData = (req, res) => {
  const floorNumber = req.body.data
  db.query('SELECT * FROM workstations WHERE floorNumber = ?',[floorNumber], (err, rows, fields) => {
    if (err) {
      console.error(err);
      return res.status(500).send('数据库查询错误');
    }
    res.json(rows)
  });
}

// 删除工位
exports.delData = (req, res) => {
  const id = req.body.data
  db.query('DELETE FROM workstations WHERE id = ?;', [id], (err, ) => {
    if (err) {
      console.error(err);
      return res.status(500).send('删除失败');
    }
    res.status(200).send('删除成功');
  });
}

// 搜索
exports.searchData = (req, res) => {
  const { workStationName, user_id, workStationFloor } = req.body.data;
  console.log(user_id);

  // 确保workStationFloor总是提供的
  if (workStationFloor === undefined || workStationFloor === null) {
    return res.status(400).send('必须提供workStationFloor');
  }

  // 初始化SQL查询语句和参数数组
  let sql = 'SELECT w.id';
  let params = [workStationFloor];  // 始终将workStationFloor添加到参数中

  // 动态构建JOIN和WHERE条件
  sql += `
    FROM workstations w
    LEFT JOIN users u ON w.user_id = u.id
    WHERE w.floorNumber = ?`;  // 始终包含workStationFloor条件

  // 添加其他条件
  let conditions = [];
  if (workStationName) {
    conditions.push('w.workStationName = ?');
    params.push(workStationName);
  }
  if (user_id !== undefined && user_id !== null) {
    conditions.push('w.user_id = ?');
    params.push(parseInt(user_id, 10));  // 确保user_id是整数
  }

  if (conditions.length > 0) {
    sql += ' AND (' + conditions.join(' OR ') + ')';  // 使用OR连接额外的条件
  }

  db.query(sql, params, (err, result) => {
    if (err) {
      console.error(err);
      return res.status(500).send('服务器错误，请稍后再试');
    }
    res.status(200).json(result);  // 使用JSON格式返回结果
  });
};

exports.getUserList = (req, res) => {
  const sqlQuery = 'SELECT id AS value, name AS label FROM users;';

  db.query(sqlQuery, (err, result) => {
    if (err) {
      console.error(err);
      return res.status(500).send('查询失败');
    }
    res.status(200).json(result);
  });
}

